package com.czy.seed.mvc.home.mapper;

import com.czy.seed.mvc.home.entity.Merchant;
import com.czy.seed.mvc.home.entity.PurchaseOrderDetailVo;
import com.czy.seed.mvc.home.entity.PurchaseOrders;
import com.czy.seed.mybatis.base.mapper.BaseMapper;
import com.czy.seed.mybatis.config.mybatis.annotations.AutoMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;


@AutoMapper
public interface PurchaseOrdersMapper extends BaseMapper<PurchaseOrders> {
    @Select(" SELECT\n" +
            "purchase_orders.ID,\n" +
            "purchase_orders.COMPANY_NAME,\n" +
            "purchase_orders.LXR,\n" +
            "purchase_orders.PHONE,\n" +
            "purchase_orders.ENTRY_NAME,\n" +
            "purchase_orders.BHDGET_AMOUNT,\n" +
            "purchase_orders.CATEGORY,\n" +
            "purchase_orders.BANKROLL_SOURCE,\n" +
            "purchase_orders.CZBKSR,\n" +
            "purchase_orders.YSXZSYSF,\n" +
            "purchase_orders.YSZFXJJ,\n" +
            "purchase_orders.YSQTZFFSSR,\n" +
            "purchase_orders.CZZHGGFSSSR,\n" +
            "purchase_orders.SJCZZYZF,\n" +
            "purchase_orders.SYSR,\n" +
            "purchase_orders.YYSR,\n" +
            "purchase_orders.SJBZSR,\n" +
            "purchase_orders.FSDWSJSR,\n" +
            "purchase_orders.QTSR,\n" +
            "purchase_orders.CGDWYJ,\n" +
            "purchase_orders.SQDWFZR,\n" +
            "purchase_orders.SQDWQMSJ,\n" +
            "purchase_orders.ZGBMFZR,\n" +
            "purchase_orders.ZGBMFZRQMSJ,\n" +
            "purchase_orders.BMYSKSSQRQM,\n" +
            "purchase_orders.BMYSKSFZRQM,\n" +
            "purchase_orders.BMYSKSFZRQMSJ,\n" +
            "purchase_orders.SZFCGBGSYJ,\n" +
            "purchase_orders.SZFCGBGSJBR,\n" +
            "purchase_orders.SZFCGBSJFZR,\n" +
            "purchase_orders.SZFCGBSJFZRQMSJ,\n" +
            "purchase_orders.TYPE,\n" +
            "purchase_orders.CREATOR_USERID,\n" +
            "purchase_orders.CREATETIME,\n" +
            "purchase_orders.CGXS,\n" +
            "purchase_orders.WTDLJG,\n" +
            "purchase_orders.ZFCGFS,\n" +
            "purchase_orders.TOTAL\n" +
            "FROM\n" +
            "purchase_orders\n" +
            "INNER JOIN seed_sys_user AS u1 ON purchase_orders.CREATOR_USERID = u1.ID\n" +
            "INNER JOIN seed_sys_user AS u2 ON u1.CREATE_BY = u2.ID\n" +
            "WHERE\n" +
            "u2.ID =#{c_id} and PURCHASE_ORDERS.TYPE= #{type}")
    List<PurchaseOrders> selectpurchaseOrdersByCreateUser(@Param("type") String type, @Param("c_id") long c_id);



    @Select(" SELECT DISTINCT purchase_orders.ID,  purchase_orders.COMPANY_NAME,  purchase_orders.LXR,  purchase_orders.PHONE,  " +
            "purchase_orders.ENTRY_NAME,  purchase_orders.BHDGET_AMOUNT,   " +
            "purchase_orders.CATEGORY,  purchase_orders.BANKROLL_SOURCE,  purchase_orders.CZBKSR,     purchase_orders.YSXZSYSF,  " +
            "purchase_orders.YSZFXJJ,  purchase_orders.YSQTZFFSSR,  purchase_orders.CZZHGGFSSSR,  purchase_orders.SJCZZYZF,   " +
            "purchase_orders.SYSR,  purchase_orders.YYSR,  purchase_orders.SJBZSR,  purchase_orders.FSDWSJSR,  " +
            "purchase_orders.QTSR,  purchase_orders.CGDWYJ,  purchase_orders.SQDWFZR,  purchase_orders.SQDWQMSJ,  " +
            "purchase_orders.ZGBMFZR,  purchase_orders.ZGBMFZRQMSJ,  purchase_orders.BMYSKSSQRQM,     purchase_orders.BMYSKSFZRQM,  " +
            "purchase_orders.BMYSKSFZRQMSJ,  purchase_orders.SZFCGBGSYJ,  purchase_orders.SZFCGBGSJBR,  purchase_orders.SZFCGBSJFZR,   " +
            "purchase_orders.SZFCGBSJFZRQMSJ,  purchase_orders.TYPE,  purchase_orders.CREATOR_USERID,  purchase_orders.CREATETIME,  " +
            "purchase_orders.CGXS,  purchase_orders.WTDLJG,  purchase_orders.ZFCGFS,  " +
            "purchase_orders.TOTAL " +
            "from purchase_orders,purchase_detailed_list,quote_or_compare, quotation,commodity,merchant,seed_sys_user\n" +
            "             where ( (purchase_orders.CREATOR_USERID=#{userId} or quotation.MERCHANT_ID=#{userId} or seed_sys_user.CREATE_BY=#{userId} ) and seed_sys_user.id=purchase_orders.CREATOR_USERID  AND purchase_orders.TYPE != '申请'\n" +
            "\t\tAND purchase_orders.type != '待审核' \n" +
            "\t\t\t\t\t\tand purchase_orders.ID=purchase_detailed_list.SSDD_GLGX  and  quote_or_compare.PURCHASE_ID=purchase_detailed_list.ID \n" +
            "\t\t\t\t\t\tand quotation.QUOTE_OR_COMPARE_ID=quote_or_compare.ID  ) \n" +
            "            or((purchase_orders.CREATOR_USERID=#{userId} or merchant.USER_ID=#{userId} or seed_sys_user.CREATE_BY=#{userId}) \n" +
            "\t\t\t\t\t\tand purchase_detailed_list.COMMODITY_ID=commodity.ID and commodity.BUSINESSES_ID=merchant.ID  \n" +
            "\t\t\t\t\t\t and seed_sys_user.id=purchase_orders.CREATOR_USERID and purchase_orders.ID=purchase_detailed_list.SSDD_GLGX AND purchase_orders.TYPE != '申请'\n" +
            "\t\tAND purchase_orders.type != '待审核' )")
    List<PurchaseOrders> selectpurchaseOrdersByCreateUser_(@Param("type") int type, @Param("userId") Long c_id, @Param("types") String types);






    @Select("SELECT DISTINCT\n" +
            "\tpurchase_orders.ID,\n" +
            "\tpurchase_orders.COMPANY_NAME,\n" +
            "\tpurchase_orders.LXR,\n" +
            "\tpurchase_orders.PHONE,\n" +
            "\tpurchase_orders.ENTRY_NAME,\n" +
            "\tpurchase_orders.BHDGET_AMOUNT,\n" +
            "\tpurchase_orders.CATEGORY,\n" +
            "\tpurchase_orders.BANKROLL_SOURCE,\n" +
            "\tpurchase_orders.CZBKSR,\n" +
            "\tpurchase_orders.YSXZSYSF,\n" +
            "\tpurchase_orders.YSZFXJJ,\n" +
            "\tpurchase_orders.YSQTZFFSSR,\n" +
            "\tpurchase_orders.CZZHGGFSSSR,\n" +
            "\tpurchase_orders.SJCZZYZF,\n" +
            "\tpurchase_orders.SYSR,\n" +
            "\tpurchase_orders.YYSR,\n" +
            "\tpurchase_orders.SJBZSR,\n" +
            "\tpurchase_orders.FSDWSJSR,\n" +
            "\tpurchase_orders.QTSR,\n" +
            "\tpurchase_orders.CGDWYJ,\n" +
            "\tpurchase_orders.SQDWFZR,\n" +
            "\tpurchase_orders.SQDWQMSJ,\n" +
            "\tpurchase_orders.ZGBMFZR,\n" +
            "\tpurchase_orders.ZGBMFZRQMSJ,\n" +
            "\tpurchase_orders.BMYSKSSQRQM,\n" +
            "\tpurchase_orders.BMYSKSFZRQM,\n" +
            "\tpurchase_orders.BMYSKSFZRQMSJ,\n" +
            "\tpurchase_orders.SZFCGBGSYJ,\n" +
            "\tpurchase_orders.SZFCGBGSJBR,\n" +
            "\tpurchase_orders.SZFCGBSJFZR,\n" +
            "\tpurchase_orders.SZFCGBSJFZRQMSJ,\n" +
            "\tpurchase_orders.TYPE,\n" +
            "\tpurchase_orders.CREATOR_USERID,\n" +
            "\tpurchase_orders.CREATETIME,\n" +
            "\tpurchase_orders.CGXS,\n" +
            "\tpurchase_orders.WTDLJG,\n" +
            "\tpurchase_orders.ZFCGFS,\n" +
            "\tpurchase_orders.TOTAL\n" +
            "FROM\n" +
            "\tpurchase_orders,\n" +
            "\tpurchase_detailed_list,\n" +
            "\tquote_or_compare,\n" +
            "\tquotation\n" +
            "WHERE\n" +
            "\t(\n" +
            "\t\t( quote_or_compare.TYPE = 1\n" +
            "\t\t\tAND purchase_orders.ID = purchase_detailed_list.SSDD_GLGX\n" +
            "\t\t\tAND quote_or_compare.PURCHASE_ID = purchase_detailed_list.ID  AND purchase_detailed_list.TYPE = '等待确认单'\n" +
            "\t\t)\n" +
            "\t\tOR (\n" +
            "\t\t\tpurchase_orders.ID = purchase_detailed_list.SSDD_GLGX\n" +
            "\t\t\tAND (purchase_detailed_list.TYPE = '等待确认单' or purchase_detailed_list.TYPE = '待完成')\n" +
            "\t\t)\n" +
            "\t)\n" +
            "AND (\n" +
            "\tquotation.MERCHANT_ID = #{MERCHANT_ID}\n" +
            "\tOR purchase_orders.CREATOR_USERID = #{USERID}\n" +
            ")")
    List<PurchaseOrders> selectMemorandInUniting(@Param("MERCHANT_ID") Long merchantId, @Param("USERID") Long userId);




    @Select("SELECT\n" +
            "\tquotation.QUOTE_UNIT,\n" +
            "\tquotation.GGXH,\n" +
            "\tquotation.JSCS,\n" +
            "\tquotation.NUM,\n" +
            "\tquotation.COMMODITY_MAK_PRC,\n" +
            "\tquotation.DJ,\n" +
            "\t(quotation.NUM * quotation.DJ) TOTAL,\n" +
            "\tpurchase_detailed_list.PMMC,\n" +
            "\tquotation.CONSINGMENT_ADRESS,\n" +
            "\tquotation.CONSINGMENT_TIME\n" +
            "FROM\n" +
            "\tpurchase_detailed_list\n" +
            "INNER JOIN quote_or_compare ON quote_or_compare.PURCHASE_ID = purchase_detailed_list.ID\n" +
            "INNER JOIN quotation ON quotation.QUOTE_OR_COMPARE_ID = quote_or_compare.ID\n" +
            "WHERE\n" +
            "\tpurchase_detailed_list.id = #{P_orderId}\n" +
//            "            AND purchase_detailed_list.TYPE = '等待确认单'\n" +
            "            AND purchase_detailed_list.RESULTVALIDATIONPATH IS NOT NULL\n" +
            "            AND quotation.ggxh IS NOT NULL\n" +
            "            AND quotation.num IS NOT NULL\n" +
            "            AND quotation.dj IS NOT NULL and quotation.type=8  and purchase_detailed_list.gmfs='二次询价' \n" +
            "union all \n" +
            "SELECT\n" +
            "seed_sys_user.`NAME` QUOTE_UNIT,\n" +
            "purchase_detailed_list.GGXH,\n" +
            "purchase_detailed_list.JSCS,\n" +
            "purchase_detailed_list.SL num,\n" +
            "commodity.COMMODITY_MAK_PRC,\n" +
            "purchase_detailed_list.DJ,\n" +
            "(purchase_detailed_list.DJ*purchase_detailed_list.SL)TOTAL,\n" +
            "purchase_detailed_list.PMMC,\n" +
            "(case when seed_sys_user.ADDRESS  is null then '        ' else seed_sys_user.ADDRESS end )CONSINGMENT_ADRESS,\n" +
            "'          ' CONSINGMENT_TIME\n" +
            "FROM\n" +
            "purchase_detailed_list\n" +
            "INNER JOIN commodity ON purchase_detailed_list.COMMODITY_ID = commodity.ID \n" +
            "INNER JOIN purchase_orders ON purchase_detailed_list.SSDD_GLGX = purchase_orders.ID \n" +
            "inner join seed_sys_user on purchase_orders.CREATOR_USERID=seed_sys_user.ID\n" +
            "where \n" +
            " purchase_orders.CREATOR_USERID = seed_sys_user.ID\n" +
            "and \n" +
            "purchase_detailed_list.id=#{P_orderId} and purchase_detailed_list.gmfs='直接购买'  AND (purchase_detailed_list.TYPE = '等待确认单' or purchase_detailed_list.TYPE = '待完成')")
    List<PurchaseOrderDetailVo> selectmemorInUniting(@Param("P_orderId") Long P_orderId);

    @Select("SELECT DISTINCT purchase_orders.ID,purchase_orders.COMPANY_NAME,\n" +
            "purchase_orders.LXR,purchase_orders.PHONE,purchase_orders.ENTRY_NAME,\n" +
            "purchase_orders.BHDGET_AMOUNT,purchase_orders.CATEGORY,purchase_orders.BANKROLL_SOURCE,\n" +
            "purchase_orders.CZBKSR,purchase_orders.YSXZSYSF,purchase_orders.YSZFXJJ,\n" +
            "purchase_orders.YSQTZFFSSR,purchase_orders.CZZHGGFSSSR,purchase_orders.SJCZZYZF,\n" +
            "purchase_orders.SYSR,purchase_orders.YYSR,purchase_orders.SJBZSR,\n" +
            "purchase_orders.FSDWSJSR,purchase_orders.QTSR,purchase_orders.CGDWYJ,\n" +
            "purchase_orders.SQDWFZR,purchase_orders.SQDWQMSJ,purchase_orders.ZGBMFZR,\n" +
            "purchase_orders.ZGBMFZRQMSJ,purchase_orders.BMYSKSSQRQM,purchase_orders.BMYSKSFZRQM,\n" +
            "purchase_orders.BMYSKSFZRQMSJ,purchase_orders.SZFCGBGSYJ,purchase_orders.SZFCGBGSJBR,\n" +
            "purchase_orders.SZFCGBSJFZR,purchase_orders.SZFCGBSJFZRQMSJ,purchase_orders.TYPE,\n" +
            "purchase_orders.CREATOR_USERID,purchase_orders.CREATETIME,purchase_orders.CGXS,\n" +
            "purchase_orders.WTDLJG,purchase_orders.ZFCGFS,purchase_orders.TOTAL\n" +
            "FROM\n" +
            "purchase_orders\n" +
            "INNER JOIN seed_sys_user AS u1 ON purchase_orders.CREATOR_USERID = u1.ID  " +
            "INNER JOIN purchase_detailed_list on purchase_orders.ID=purchase_detailed_list.SSDD_GLGX\n" +
            "WHERE\n" +
            "u1.id = #{id} AND\n" +
            "purchase_orders.TYPE =#{type}")
    List<PurchaseOrders> selectpurchaseOrdersByUserbycreate(@Param("id") long userId, @Param("type") String type);

    @Update(" update purchase_orders  set type=#{type},updatetime=sysdate(),updateuserid=#{c_userid}  where id=#{id}")
    void updateById(@Param("type") String type, @Param("id") long id, @Param("c_userid") long c_userid);


}
